<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<%@ page language="java" import="java.sql.*"%>

<%
	Class.forName("org.postgresql.Driver");
	Connection conn = DriverManager.getConnection(
		"jdbc:postgresql://localhost:5432/cse132b", "cse132blogin",
		"1234567890");
	Statement stmt;
	int ID, DegreeID;

	ID = Integer.valueOf(request.getParameter("ID"));
	DegreeID = Integer.valueOf(request.getParameter("degrees"));

	stmt = conn.createStatement();
	ResultSet rsDegree = stmt
			.executeQuery("SELECT * FROM degrees_offered WHERE degrees_id =" + String.valueOf(DegreeID));

	int lower_units_reqd = 0, upper_units_reqd=0, elective_units_reqd=0;
	String DegreeName = "";
	while (rsDegree.next()) {
		DegreeName = rsDegree.getString("name");
		lower_units_reqd = rsDegree.getInt("lower_units_reqd");
		upper_units_reqd = rsDegree.getInt("upper_units_reqd");
		elective_units_reqd = rsDegree.getInt("elective_units_reqd");
	}
	
	stmt = conn.createStatement();
	ResultSet rs = stmt
			.executeQuery("SELECT * FROM students WHERE student_id =" + String.valueOf(ID));

	String myFirstName = "", myMiddleName = "", myLastName = "";
	while (rs.next()) {
		myFirstName = rs.getString("first_name");
		myMiddleName = rs.getString("middle_name");
		myLastName = rs.getString("last_name");
	}

	
	rs.close();
	stmt.close();
	
	stmt = conn.createStatement();
	ResultSet rsConcentration = stmt.executeQuery("SELECT * FROM category WHERE category_id > 3");

	String HTML="";
	
	while (rsConcentration.next()) {

		Statement stmt1 = conn.createStatement();
		ResultSet rs1 = stmt1.executeQuery("SELECT * FROM (SELECT * FROM (SELECT *, SUBSTRING(enroll.grade_letter,1,1) AS grade_letter_without_sign FROM students, ms , enroll, classes WHERE students.student_id = ms.student_id AND students.student_id = enroll.student_id AND classes.class_id = enroll.section_id AND quarter = '2' and year = 2014 AND students.student_id =" + String.valueOf(ID) + " AND classes.course_id IN (select courses.course_id from course_category, courses where courses.course_id = course_category.course_id AND course_category.category_id in (select category_id from category where category.category_id = " + rsConcentration.getString("category_id") + "))) AS AAA WHERE grade_letter_without_sign IN ('A','B','C','P')) AS AAA  LEFT JOIN GRADE_CONVERSION ON AAA.grade_letter = GRADE_CONVERSION.letter_grade");
		
		String ListCourseHasTaken = "";
		double GPA = 0, myGrade=0;
		int TotalUnits = 0;
		while (rs1.next()) {
			String s;
			s = rs1.getString("grade_letter");
			if (s==null) s="";
			
			if (!s.equals("IN"))
			{
				TotalUnits += rs1.getInt("units");
			}
			myGrade += (rs1.getFloat("number_grade") * rs1.getInt("units"));
			
			ListCourseHasTaken += rs1.getString("title") + ",&nbsp;";
		}
		if (TotalUnits > 0)
		{
			GPA = myGrade / TotalUnits;
			ListCourseHasTaken += " <br>Earn Units: " + String.valueOf(TotalUnits);
			ListCourseHasTaken += " - GPA: " + String.format("%4.2f", GPA);
		}


		rs1.close();
		stmt1.close();		
		
		Statement stmt2 = conn.createStatement();
		ResultSet rs2 = stmt2.executeQuery("select * from course_category, courses where courses.course_id = course_category.course_id AND course_category.category_id in (select category_id from category where category.category_id = " + rsConcentration.getString("category_id") + ") AND courses.course_id NOT IN (SELECT course_id FROM students, ms , enroll, classes WHERE students.student_id = ms.student_id AND students.student_id = enroll.student_id AND classes.class_id = enroll.section_id AND quarter = '2' and year = 2014 AND students.student_id =" + String.valueOf(ID) + ")");
		
		String ListCourse = "";
		while (rs2.next()) {
			ListCourse += rs2.getString("course_number") + ",&nbsp;";
		}
		rs2.close();
		stmt2.close();
		
		HTML += "<tr>";
		HTML += "	<td>";
		HTML += rsConcentration.getString("name") + " <br> GPA: " + rsConcentration.getString("minimungpa") + " <br> Units: " + rsConcentration.getString("min_units");
		HTML += "	</td>";
		HTML += "	<td>";
		HTML += ListCourse;
		HTML += "	</td>";
		HTML += "	<td>";
		HTML += ListCourseHasTaken;
		HTML += "	</td>";
		HTML += "</tr>";
	}
	rsConcentration.close();
	stmt.close();
	
	
	
%>
<!DOCTYPE html>
<html>
<head>
</head>
<body>
	<table border="1">
		<tr>
			<td valign="top" width="30%">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="/menu.html" />
			</td>
			<td>
				<p align="center">
					<font size="6">Display the classes currently taken by
						student</font>
				</p> <br> Full name: <%
 	out.print((myFirstName + " " + myMiddleName + " " + myLastName));
 %>
				<br>
				<br>
				<table border="0">
					<tr>
						<td>
							Degree Name: <%=DegreeName%>
						</td>
					</tr>
					<tr>
						<td>
							<table border="1">
								<tr>
									<th>
										Concentration Name
									</th>
									<th>
										 List the set of courses that the student has NOT yet taken
									</th>
									<th>
										 List the set of courses that the student has yet taken
									</th>
								</tr>
								<%=HTML%>
							</table>
						</td>
					</tr>


				</table>
				
			</td>
		</tr>
	</table>
</body>
<%
	// Close the Connection
	conn.close();
%>